﻿using LY.DataAccess.Repository;
using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
    public class ProjectRepository : BaseRepository
    {
        public Entity.PageSearch.PagedList<Entity.R_ProjectClass> QueryProjectClass(Entity.PageSearch.PageView view)
        {
            string cols = @"[Id]
                      ,[Sequence]
                      ,[ParentId]
                      ,[Name] ";
            return base.PageGet<R_ProjectClass>(view, cols, @"[R_ProjectClass] ", "", "[Id] ASC", "");

        }

        public PagedList<Entity.R_ProjectClass> QueryAllProjectClass(PageView view)
        {
            string cols = @"[Id]
                      ,[Sequence]
                      ,[ParentId]
                      ,[Name] ";
            return base.PageGet<R_ProjectClass>(view, cols, @"[R_ProjectClass] ", "", "[Id] ASC", "");

        }

        public int SaveProjectClass(R_ProjectClass model)
        {
            if (model.Id == 0)//新增
            {
                return (int)base.Insert(model);
            }
            else
            {
                return (int)base.Update(model);
            }
        }

        public int Insert(R_ProjectClass model)
        {
            return (int)base.Insert(model);
        }

        public int Uodate(R_ProjectClass model)
        {
            return (int)base.Update(model);
        }

        public int DeleteProjectClass(int id)
        {
            string sql = "delete from R_ProjectClass where Id=@ID";
            return base.Excute(sql, new { ID = id });
        }

        public R_ProjectClass GetModel(int Id)
        {
            string sql = "select Id,Name,ParentId,Sequence from R_ProjectClass where Id=@ID";
            return base.Get<R_ProjectClass>(sql, new { ID = Id });
        }

        public List<R_ProjectClass> GetProjectClassListModel()
        {
            string sql = "select Id,Name,ParentId,Sequence from R_ProjectClass";
            return base.Query<R_ProjectClass>(sql, null);
        }

        public PagedList<ProjectEx> QueryProject(ProjectSearch search, PageView view)
        {
            const string cols = @"[Id],[BiaoNo],[ProjectName],[ProjectClass],[Amount],[CollectDays],[ProjectAdWord],[ProjectSummary],[ProjectDetails],[VideoAddr],[ProjectThumImage]
                            ,[ProjectThumImage1],[AddTime],[ProjectStatus],[AuditTime],[RefuseReason],[MemberId],[UserId] ,(select name from (select name,code from S_DictItem where DirID = (select id from S_DictDir where Code = 'ProjectStatus')) a where Code =[AuditStatus]) as AuditStatusName, AuditStatus
                         ,( select  name from R_ProjectClass where Id=[ProjectClass]) as [ProjectClassName]
                             ,(select RealName from U_Member where ID= R_Project.MemberID) as MemberName 
                      ,( select UserName from S_User where ID=[UserId]) as  AdminName  ";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.ProjectStatus))
            {
                sqlWhere += " and ProjectStatus= " + search.ProjectStatus + " ";
            }
            if (!string.IsNullOrEmpty(search.AuditStatus))
            {
                if (search.AuditStatus == "110")
                {
                    sqlWhere += " and AuditStatus in (110,139) ";
                }
                else if (search.AuditStatus == "140")
                {
                    sqlWhere += " and AuditStatus in (140,149,199,209) ";
                }
                else if (search.AuditStatus == "150")
                {
                    sqlWhere += " and AuditStatus in (150) ";
                }
                else if (search.AuditStatus == "200")
                {
                    sqlWhere += " and AuditStatus =200 ";
                }
                else if (search.AuditStatus == "210")
                {
                    sqlWhere += " and AuditStatus =210 ";
                }
                else
                {
                    sqlWhere += " and AuditStatus =-100 ";
                }

            }
            return base.PageGet<ProjectEx>(view, cols, @"R_Project ", sqlWhere, "Id", view.GetSqlOrder());
        }

        public R_Project GetProject(int? id)
        {
            string sql = @"select [Id],[BiaoNo],[ProjectName],[ProjectClass],[Amount],[CollectDays],[ProjectAdWord],[ProjectSummary],[ProjectDetails],[VideoAddr],[ProjectThumImage]
                            ,[ProjectThumImage1],[ProjectThumImage2],[AddTime],[ProjectStatus],[AuditTime],[RefuseReason],[MemberId],[UserId],AuditStatus,BeginTime from R_Project 
                            where Id=@Id ";
            return base.Get<R_Project>(sql, new { Id = id });
        }

        public int ProjectSave(R_Project project)
        {
            if (project.Id <= 0)
            {
                return (int)base.Insert(project);
            }
            else
            {
                return (int)base.Update(project);
            }
        }

        public PagedList<R_ProjectItem_EX> QueryProjectItem(PageView view, int projectId)
        {
            string cols = @"o.[Id],ProjectName,[ProjectId],o.[Amount],[MRedoundContent],[RedoundType],[RedoundContent],[RedoundImage] ,[Limits] ,[Freight] ,[DedoundDays] ,[Createtime]";
            string sqlWhere = "";
            if (projectId > 0)
            {
                sqlWhere += " and o.ProjectId= " + projectId + " ";
            }
            return base.PageGet<R_ProjectItem_EX>(view, cols, @"[R_ProjectItem] o left join R_Project r on o.ProjectId=r.Id ", sqlWhere, "o.[Id] ASC", "");
        }

        //查询项目档位的某一项
        public R_ProjectItem GetProjectItem(int id)
        {
            string sql = @"select [Id],[ProjectId],[Amount],[MRedoundContent],[RedoundType],[RedoundContent],[RedoundImage],[Limits],[Freight],[DedoundDays],[Createtime]
                           from R_ProjectItem where Id = @Id";
            return base.Get<R_ProjectItem>(sql, new { Id = id });
        }

        //查询订单
        public PagedList<R_ProjectOrder_EX> QueryProjectOrder(PageView view, ProjectOrderSearch search)
        {
            string cols = @"o.[Id],[ProjectId],ProjectName,[ProjectItemId],o.[Amount],o.[MemberId] ,[PayTime],[PayType],o.[AddTime],[SerialNumber],[Contacts],[ContactNumber]
                           ,[Remark],[PayStatus]";
            string sqlWhere = "";
            if (search.ProjectId > 0)
            {
                sqlWhere += " and o.ProjectId= " + search.ProjectId + " ";
            }
            if (search.ProjectItemId > 0)
            {
                sqlWhere += " and o.ProjectItemId= " + search.ProjectItemId + " ";
            }
            if (search.Amount != 0)
            {
                sqlWhere += " and o.Amount= " + search.Amount + " ";
            }
            if (search.PayStatus > 0)
            {
                sqlWhere += " and o.PayStatus= " + search.PayStatus + " ";
            }
            return base.PageGet<R_ProjectOrder_EX>(view, cols, @"[R_ProjectOrder] o left join R_Project r on o.ProjectId=r.Id ", sqlWhere, "o.[Id] ASC", "");
        }

        //查询某个投资订单
        public R_ProjectOrder GetProjectOrder(int id)
        {
            string sql = @"select [Id],[ProjectId],[ProjectItemId],[Amount],[MemberId],[PayTime],[PayType],[AddTime],[SerialNumber],[Contacts]
                         ,[ContactNumber],[Remark],[PayStatus],[NeedInvoice],[InvoiceTitle] from R_ProjectOrder where Id=@Id";
            return base.Get<R_ProjectOrder>(sql, new { Id = id });
        }

        //查询某个项目的支持人数
        public int GetOrderCount(int projectId)
        {
            string sql = "select count(*) from R_ProjectOrder where ProjectId=@ProjectId";
            return base.Get<int>(sql, new { ProjectId = projectId });
        }

        //查询每个项目一共筹集多少资金
        public decimal GetAllAmount(int projectId)
        {
            string sql = "select sum(amount) from R_ProjectOrder where ProjectId=@ProjectId and PayStatus in(1,3)";
            try
            {
                return base.Get<decimal>(sql, new { ProjectId = projectId });
            }
            catch (Exception)
            {
                return (decimal)0.00;
            }
        }

        //查询某个人一共发起过几个项目
        public int GetProjectCount(int memberId)
        {
            string sql = "select count(*) from R_Project where MemberId=@MemberId";
            return base.Get<int>(sql, new { MemberId = memberId });
        }

        //查询项目拓展列表
        public List<R_Project_EX> QueryProject_EXList(ProjectSearch search)
        {
            string sql = @"select  p.Id,
		                    ProjectThumImage,
		                    ProjectStatus,
		                    ProjectAdWord,
                            ProjectClass,
		                    Amount,
		                    po.CurrentAmount,
		                    po.CurrentAmount/p.Amount*100 as ProjectPercent,
		                    abs(p.CollectDays-datediff( day,p.AuditTime, GETDATE())) as RemainDays 
		                    from R_Project p
		                    left join 
		                    (select R_ProjectOrder.ProjectId,SUM(R_ProjectOrder.Amount) as CurrentAmount from R_ProjectOrder group by ProjectId) as po
		                    on p.Id=po.ProjectId where AuditStatus=210  ";

            if (search.ProjectClassId != null)
            {
                sql += " and ProjectClass=@ProjectClass ";
                return base.Query<R_Project_EX>(sql, new { ProjectClass = search.ProjectClassId });
            }
            else
            {
                return base.Query<R_Project_EX>(sql, null);
            }
        }

        public PagedList<ProjectEx> QueryProject_EXList(ProjectSearch search, PageView view)
        {
            const string cols = @"*  ";

            //项目状态
            //string sqlWhere = " and ProjectStatus in (2,4,5)";
            string sqlWhere = "";


            if (!string.IsNullOrEmpty(search.AuditStatus))
            {
                sqlWhere += " and AuditStatus= " + search.AuditStatus + " ";

            }
            else
            {
                sqlWhere += " and (AuditStatus=210 or AuditStatus=220 or AuditStatus=219)";
            }

            /* if (!string.IsNullOrEmpty(search.status))
                {
                    sqlWhere += " and ProjectStatus= " + search.status + " ";
                }*/

            if (!string.IsNullOrEmpty(search.ProjectClassId))
            {
                sqlWhere += " and ProjectClass= " + search.ProjectClassId + " ";
            }

            if (search.MemberId != 0)
            {
                sqlWhere += " and MemberId= " + search.MemberId + " ";
            }
            return base.PageGet<ProjectEx>(view, cols, "View_GetProducts", sqlWhere, "p.Id", view.GetSqlOrder());
        }
        //我发起的项目
        public PagedList<ProjectEx> QueryProjectMyProject_EXList(ProjectSearch search, PageView view)
        {
            const string cols = @"* ";

            //项目状态
            string sqlWhere = "";//" and AuditStatus in (0,1,2,3,4,5)";

            if (!string.IsNullOrEmpty(search.status))
            {
                sqlWhere += " and ProjectStatus= " + search.status + " ";
            }

            if (!string.IsNullOrEmpty(search.ProjectClassId))
            {
                sqlWhere += " and ProjectClass= " + search.ProjectClassId + " ";
            }

            if (search.MemberId != 0)
            {
                sqlWhere += " and MemberId= " + search.MemberId + " ";
            }
            if (search.AuditStatus != null)
            {
                if (search.AuditStatus == "-2")
                {
                    sqlWhere += " and AuditStatus >109 and AuditStatus <210";
                }
                else
                {
                    sqlWhere += " and AuditStatus= " + search.AuditStatus + " ";
                }
            }


            return base.PageGet<ProjectEx>(view, cols, "View_GetProducts", sqlWhere, "p.Id", view.GetSqlOrder());
        }



        //查询我关注的项目
        public PagedList<ProjectEx> QueryProjectAttention_EXList(ProjectSearch search, PageView view)
        {
            const string cols = @" View_GetProducts.*,pAtt.Id as pid";

            string sqlWhere = " and AuditStatus in (210,219,220)";
            if (search.Member != 0)
            {
                sqlWhere += " and pAtt.Member= " + search.Member + " ";
            }
            return base.PageGet<ProjectEx>(view, cols, "View_GetProducts right join R_ProjectAttention as pAtt on View_GetProducts.Id=pAtt.Projectid", sqlWhere, "pAtt.Id", view.GetSqlOrder());

        }

        //我支持的项目
        public PagedList<ProjectEx> QueryBoughtProject(ProjectSearch search, PageView view)
        {
            const string cols = @"p.Id
                            ,p.ProjectName
                            ,p.BiaoNo
                            ,ProjectThumImage
                            ,ProjectStatus
                            ,CASE WHEN AuditStatus = 0 THEN '已保存'
                            WHEN (AuditStatus <210 and AuditStatus >109) THEN '审核中'
                            WHEN AuditStatus = 210 THEN '已审核' 
                            WHEN AuditStatus = 109 THEN '不通过'
                            WHEN AuditStatus=220 THEN '众筹成功' 
                            WHEN AuditStatus=219 THEN '众筹失败' 
                            END as ProjectStatusName
                            ,ProjectAdWord
                            ,ProjectClass
                            ,Amount
                            ,ZCamount
                            ,PayStatus
                            ,SerialNumber
                            ,UserAddress
                            ,ItemFreight
                            ,'¥'+ (CAST(ISNULL(po2.CurrentAmount,0) as nvarchar(20)))  as CurrentAmount
                            ,(Convert(varchar(50),CAST( Round(ISNULL( po2.CurrentAmount,0)/p.Amount*100,1) as decimal(18,2)))+'%') as ProjectPercent
                            ,Convert(varchar(50)
                            ,isnull(abs(p.CollectDays-datediff( day,p.AuditTime, GETDATE())),0)) + '天' as RemainDays ";


            string sqlWhere = " and AuditStatus in (210)";
            if (search.MemberId > 0)
            {
                sqlWhere += " and po.MemberId= " + search.MemberId + " ";
            }
            if (!string.IsNullOrEmpty(search.ProjectStatus))
            {
                sqlWhere += " and ProjectStatus= " + search.ProjectStatus + " ";
            }
            if (!string.IsNullOrEmpty(search.PayStatus))
            {
                sqlWhere += " and po.PayStatus= " + search.PayStatus + " ";
            }
            return base.PageGet<ProjectEx>(view, cols, @"  R_Project p left join 
                                            (select ItemFreight,  MemberId,
                                            UserAddress,R_ProjectOrder.ProjectId,
                                          Amount as ZCamount,SerialNumber,PayStatus
                                                     from R_ProjectOrder group by
                                                     ProjectId,ItemFreight,MemberId,UserAddress,Amount,SerialNumber,PayStatus) as po on p.Id=po.ProjectId left join 
                                                     (select SUM(R_ProjectOrder.Amount) as CurrentAmount,ProjectId from R_ProjectOrder where PayStatus in(1,3) group by
                                                     ProjectId) as po2 on p.Id=po2.ProjectId", sqlWhere, " AddTime desc", "");
        }
        //查询出一个商品拓展类
        public R_Project_EX GetProject_EX(int id)
        {
            string sql = @"select  p.Id,
                            ProjectName,           
		                    ProjectThumImage,
                            ProjectThumImage1,
		                    ProjectStatus,
		                    ProjectAdWord,
                            ProjectClass,
		                    Amount,
                            CollectDays,
                            MemberId,
                            AuditTime,
                            VideoAddr,
                            ProjectDetails,
		                    po.CurrentAmount,
		                    po.CurrentAmount/p.Amount*100 as ProjectPercent,
		                    abs(p.CollectDays-datediff( day,p.AuditTime, GETDATE())) as RemainDays 
		                    from R_Project p
		                    left join 
		                    (select R_ProjectOrder.ProjectId,SUM(R_ProjectOrder.Amount) as CurrentAmount from R_ProjectOrder group by ProjectId) as po
		                    on p.Id=po.ProjectId where Id=@Id ";
            return base.Get<R_Project_EX>(sql, new { Id = id });
        }

        //查询每个档位投资信息
        public List<R_ProjectOrder_EX> QueryInvestmentByItem(int projectId)
        {
            //            string sql = @"select item.ProjectId
            //                            ,item.Id as ItemId
            //                            ,count(po.ProjectItemId) as PersonCount
            //                            ,SUM(po.Amount)as AmountCount
            //                            
            //                            ,item.Amount as ItemAmount
            //                            ,item.Limits as Limits
            //                            ,item.MRedoundContent as returnContent
            //                            ,item.RedoundImage
            //                            ,item.Freight
            //                            ,item.DedoundDays
            //                            from R_ProjectItem as item left join R_ProjectOrder as po on item.Id = po.ProjectItemId   
            //                            group by item.ProjectId,item.Id,item.Amount,item.Limits,item.MRedoundContent,item.RedoundImage,item.Freight,item.DedoundDays
            //                            having item.ProjectId=@ProjectId";
            string sql = @"select item.ProjectId
        ,item.Id as ItemId
        ,count(po.ProjectItemId) as PersonCount
        ,SUM(po.Amount)as AmountCount
        
        ,item.Amount as ItemAmount
        ,item.Limits as Limits
        ,item.MRedoundContent as returnContent
        ,item.RedoundImage
        ,item.Freight
        ,item.DedoundDays
        from R_ProjectItem as item left join (select ProjectItemId,Amount,PayStatus from R_ProjectOrder where PayStatus=1) as po on item.Id = po.ProjectItemId  
        group by item.ProjectId,item.Id,item.Amount,item.Limits,item.MRedoundContent,item.RedoundImage,item.Freight,item.DedoundDays,po.PayStatus
        having item.ProjectId=@ProjectId ";
            return base.Query<R_ProjectOrder_EX>(sql, new { ProjectId = projectId });
        }

        //查询最近投资本项目的人员列表
        public List<U_Member_EX> QueryInvestmentMember(int projectId)
        {
            string sql = @"select top 10 Name,Nickname,HeadImage,PayTime,SerialNumber,Amount
                            from U_Member inner join R_ProjectOrder on U_Member.ID=R_ProjectOrder.MemberId
                            where ProjectId =@ProjectId and PayStatus=1 and PayTime is not null
                            order by PayTime desc";
            return base.Query<U_Member_EX>(sql, new { ProjectId = projectId });
        }

        //获取支持人列表
        public PagedList<U_Member_EX> QueryInvestmentMemberList(PageView view, int projectId)
        {
            string cols = @" m.[ID],[Name],[HeadImage],o.[Amount],[PayTime],SerialNumber,BiaoNo";

            string sqlWhere = " and PayTime is not null and PayStatus in(1,3,4) and ProjectId=" + projectId;
            return base.PageGet<U_Member_EX>(view, cols,
                                            @"U_Member m inner join R_ProjectOrder o on m.ID=o.MemberId left join R_Project p on o.ProjectId =p.Id",
                                            sqlWhere, "[PayTime] desc", "");

        }

        //确认收货
        public int ConfirmOrder(string id)
        {
            string sql = "update R_ProjectOrder set PayStatus=3 where SerialNumber=@Id";
            return base.Excute(sql, new { Id = id });
        }

        //根据当前会员ID查询与之对应的项目
        public PagedList<R_Project_EX> GetProjectList(int id, PageView view)
        {
            string cols = @"[ProjectName]
                                  ,[ProjectClass]
                                  ,[Amount]
                                  ,[CollectDays]
                                  ,[ProjectAdWord]
                                  ,[ProjectSummary]
                                  ,[ProjectDetails]
                                  ,[VideoAddr]
                                  ,[ProjectThumImage]
                                  ,[ProjectThumImage1]
                                  ,[ProjectStatus]
                                  ,[AuditTime]
                                  ,[RefuseReason]
                                  ,[MemberId]
                                  ,[UserId]
                                  ,[R_ProjectAttention].[Id]
                                  ,[Projectid]
                                  ,[Member]
                                  ,[R_ProjectAttention].[Addtime]
                                  ,[Type]";
            string sqlWhere = " and [R_ProjectAttention].Member=" + id;
            return base.PageGet<R_Project_EX>(view, cols, @"[R_Project] inner join [R_ProjectAttention] on [R_Project].[Id]=[R_ProjectAttention].[Projectid]", sqlWhere, "[R_Project].[Id] ASC", "");
        }

        //        public PagedList<ProjectEx> QueryMyProject(ProjectSearch search, PageView view)
        //        {
        //            const string cols = @"p.Id, p.ProjectName,		ProjectThumImage,		ProjectStatus,
        //		CASE 		WHEN ProjectStatus =0 THEN '已保存'
        //		WHEN ProjectStatus= 1 THEN '审核中' 		WHEN ProjectStatus= 2 THEN '已审核'
        //		WHEN ProjectStatus= 3 THEN '不通过'		WHEN ProjectStatus= 4 THEN '已结束'
        //		WHEN ProjectStatus= 5 THEN '已成功'		WHEN ProjectStatus= -1 THEN '已删除' 		END as ProjectStatusName, 
        //	  ProjectAdWord,		ProjectClass,		Amount,
        //		'¥'+ CAST(ISNULL(po.CurrentAmount,0) as nvarchar(20))  as CurrentAmount,  
        //		(Convert(varchar(50),CAST( Round(ISNULL( po.CurrentAmount,0)/p.Amount*100,1) as decimal(4,2)))+'%') as ProjectPercent,
        //	   Convert(varchar(50),	 isnull(abs(p.CollectDays-datediff( day,p.AuditTime, GETDATE())),0)) + '天' as RemainDays  ";

        //            string sqlWhere = "";
        //            if (!string.IsNullOrEmpty(search.ProjectStatus))
        //            {
        //                sqlWhere += " and ProjectStatus= " + search.ProjectStatus + " ";
        //            }
        //            if (search.Member != 0)
        //            {
        //                sqlWhere += " and Member= " + search.Member + " ";
        //            }
        //            return base.PageGet<ProjectEx>(view, cols, @" R_Project p
        //		                    left join 
        //		                    (select R_ProjectOrder.ProjectId,SUM(R_ProjectOrder.Amount) as CurrentAmount from R_ProjectOrder group by ProjectId) as po
        //		                    on p.Id=po.ProjectId left join [R_ProjectAttention] on p.Id=[R_ProjectAttention].ProjectId ", sqlWhere, "p.Id", "");
        //        }

        public R_ProjectExpand QueryProjectExpand(int projectid)
        {
            var projectex = base.Get<R_ProjectExpand>("select * from R_ProjectExpand where ProjectId=@ProjectId",
                                      new { ProjectId = projectid });

            return projectex;

        }

        public R_ProjectExpand SaveProjectExpand(int projectid, int type)
        {
            //赞 
            var projectex = QueryProjectExpand(projectid);
            //新增
            if (projectex == null)
            {
                projectex = new R_ProjectExpand();
                if (type == 1)
                {
                    projectex.zan = 1;
                    projectex.ProjectId = projectid;
                    base.Insert(projectex);
                }
                else if (type == 2)
                {
                    projectex.guanzhu = 1;
                    projectex.ProjectId = projectid;
                    base.Insert(projectex);
                }
            }
            else
            {
                if (type == 1)
                {
                    projectex.zan += 1;
                    projectex.ProjectId = projectid;
                    base.Update(projectex);
                }
                else if (type == 2)
                {
                    projectex.guanzhu += 1;
                    projectex.ProjectId = projectid;
                    base.Update(projectex);
                }
            }
            return projectex;
        }

        //取消关注，关注减1 
        public R_ProjectExpand CancelProjectExpand(int projectid)
        {
            var projectex = QueryProjectExpand(projectid);
            projectex.guanzhu -= 1;
            projectex.ProjectId = projectid;
            base.Update(projectex);
            return projectex;
        }



        public int SaveProjectDatelinetree(R_ProjectDatelinetree model)
        {
            return model.Id <= 0 ? (int)base.Insert(model) : (int)base.Update(model);
        }

        public IList<R_ProjectDatelinetree> QueryProjectDatelinetree(int projectid)
        {
            const string strSql = "select * from R_ProjectDatelinetree where projectId=@ProjectId order by addtime desc";
            return base.Query<R_ProjectDatelinetree>(strSql, new { ProjectId = projectid });
        }

        public R_ProjectDatelinetree GetProjectDatelinetree(int id)
        {
            const string strSql = "select * from R_ProjectDatelinetree where Id=@Id";
            return base.Get<R_ProjectDatelinetree>(strSql, new { Id = id });
        }

        public bool IsProjectOwnedUser(int pid, int uid)
        {
            const string strSql = "select * from R_Project where Id=@ProjectId and MemberId=@MemberId ";
            var model = base.Get<R_Project>(strSql, new { ProjectId = pid, MemberId = uid });
            if (model == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public List<R_Project_EX> CainiXihuang()
        {
            const string strSql = @" select  top 4 
                                    ISNULL(px.guanzhu, 0) AS guanzhu, ISNULL(px.zan, 0) AS zan, ISNULL(pzc.zc, 0) AS zc,
                                     p.Id, p.ProjectName,		ProjectThumImage,		ProjectStatus,
		                                    CASE WHEN AuditStatus = 0 THEN '已保存'
											WHEN (AuditStatus <210 and AuditStatus >109) THEN '审核中'
											WHEN AuditStatus = 210 THEN '已审核' 
											WHEN AuditStatus = 109 THEN '不通过'
											WHEN AuditStatus=220 THEN '众筹成功' 
											WHEN AuditStatus=219 THEN '众筹失败' 
                                            END as ProjectStatusName, 
	                                        ProjectAdWord,		ProjectClass,		Amount,
		                                    '¥'+ CAST(ISNULL(po.CurrentAmount,0) as nvarchar(20))  as strCurrentAmount,  
		                                    (Convert(varchar(50),CAST( Round(ISNULL( po.CurrentAmount,0)/p.Amount*100,1) as decimal(4,2)))+'%') as strProjectPercent,
	                                       Convert(varchar(50),	 isnull(abs(p.CollectDays-datediff( day,p.AuditTime, GETDATE())),0)) + '天' as strRemainDays 
		                                                        from R_Project p
		                                                        left join 
		                                                        (select R_ProjectOrder.ProjectId,SUM(R_ProjectOrder.Amount) as CurrentAmount from R_ProjectOrder where PayStatus=1  group by ProjectId) as po
		                                                        on p.Id=po.ProjectId 
		                                                        left join R_ProjectExpand px on p.Id=px.ProjectId
		                                                       left join (
								                                    select ProjectId ,COUNT(1) as zc  from R_ProjectOrder   WHERE (PayStatus IN (1, 9, 11)) group by ProjectId
		                                                        ) as pzc on pzc.ProjectId=p.id 
		                                                        where AuditStatus=210   Order By NewID() ";
            return base.Query<R_Project_EX>(strSql, null);
        }

        public List<R_ProjectQuestion_EX> GetMyQuestionList(string mid, string pid)
        {
            string sql = @" select q.*,u.Name,u.HeadImage from R_projectQuestion q 
                            left join U_Member u on q.MemberId=u.ID  where ProjectId=@ProjectId
                            and q.MemberId=@MemberId order by CreateTime ASC";

            return base.Query<R_ProjectQuestion_EX>(sql, new { MemberId = mid, ProjectId = pid });
        }

        public List<R_ProjectQuestion_EX> GetQuestionList(string id, string mid)
        {
            string sql = @"select Id,ReplyId,[Message],ProjectId,MemberId,RMemberId,
                           CreateTime,Phone from dbo.R_projectQuestion where ProjectId=@ProjectId and MemberId=@MemberId";

            return base.Query<R_ProjectQuestion_EX>(sql, new { ProjectId = id, MemberId = mid });
        }

        public List<R_ProjectQuestion_EX> GetQuestionList(string pid)
        {
            string sql = "select * from  dbo.View_Question where ProjectId=@ProjectId";

            return base.Query<R_ProjectQuestion_EX>(sql, new { ProjectId = pid });
        }

        public int GetQIdByMemberId(string mid, int? type)
        {
            string sql = "select top 1 Id,ReplyId,RMemberId from R_projectQuestion where MemberID=@MemberID order by CreateTime DESC";

            List<R_ProjectQuestion_EX> plist = base.Query<R_ProjectQuestion_EX>(sql, new { MemberID = mid });

            if (plist.Count > 0)
            {
                if (string.IsNullOrEmpty(plist[0].RMemberId.ToString()))
                {
                    if (type == null)
                    {
                        return plist[0].ReplyId ?? 0;
                    }
                    else
                    {
                        return plist[0].Id;
                    }
                }
                else
                {
                    if (type == null)
                    {
                        return plist[0].Id;
                    }
                    else
                    {
                        return plist[0].ReplyId ?? 0;
                    }
                }
            }
            else
            {
                return 0;
            }
        }

        public PagedList<R_ProjectQuestion_EX> QuestionManagement(PageView view, string id)
        {
            string cols = @"Id,[Message],ProjectId,ProjectName,MemberId,Name,
                            ReplyId,CreateTime,ReplyTime,Phone ";
            string sqlWhere = string.Format(" and ProjectId={0}", id);
            return base.PageGet<R_ProjectQuestion_EX>(view, cols,
                            @"dbo.View_QuestionList ",
            sqlWhere, "CreateTime DESC", "");
        }

        public int DeleteQuestion(string mid, string pid)
        {
            string sql = "delete from dbo.R_projectQuestion where MemberId=@MemberId and ProjectId=@ProjectId";

            return base.Excute(sql, new { MemberId = mid, ProjectId = pid });
        }

        //删除已经保存的项目
        public int DeleteSaveProject(int id)
        {
            string sql = "delete from R_Project where Id=@Id";
            return base.Excute(sql, new { Id = id });
        }


        /*
        public int AdoptQuestion(string id)
        {
            string sql = "update dbo.R_projectQuestion set Status=1 where Id=@Id";

            return base.Excute(sql, new { id = id });
        }*/

        public R_ProjectOrder GerProjectOrderBySerialNumber(string serialNumber)
        {
            string sql = @"select Id, ProjectId, ProjectItemId, Amount, MemberId, PayTime, PayType, AddTime, SerialNumber, Contacts, ContactNumber
                            , Remark, PayStatus, NeedInvoice,UserAddress,ItemFreight,InvoiceTitle 
                           from R_ProjectOrder where SerialNumber=@SerialNumber";
            return base.Get<R_ProjectOrder>(sql, new { SerialNumber = serialNumber });
        }

        //修改订单状态
        public int UpdateOrderPayStatus(int status, string orderid)
        {
            string sql = "update R_ProjectOrder set PayStatus=@PayStatus where SerialNumber=@SerialNumber ";
            return base.Excute(sql, new { PayStatus = status, SerialNumber = orderid });
        }

        //根据订单编号删除订单
        public int DeleteOrder(string orderid)
        {
            string sql = "delete from R_ProjectOrder where SerialNumber=@SerialNumber";
            return base.Excute(sql, new { SerialNumber = orderid });
        }

        /// <summary>
        /// 获取项目投资总数
        /// </summary>
        /// <returns></returns>
        public ProjectItem GetProjectItem()
        {
            const string strSql = @"WITH 
                        ProjectCount AS
                         (
                            SELECT COUNT(1) as ProjectCount from R_Project where ProjectStatus not in(0,3,-1)  
                        ),
                        CurrentAmounts AS
                         (
                            SELECT    COUNT(1) as CurrentAmount, SUM(o.Amount) AS OrderCurrentCount
                                                    FROM dbo.R_ProjectOrder o
                                                 where o.PayStatus in(1,3)
                        )  
                        SELECT * FROM ProjectCount,CurrentAmounts  ";
            return base.Get<ProjectItem>(strSql, null);
        }


        public PagedList<ProjectEx> QueryHotProject(ProjectSearch search, PageView view)
        {
            const string cols = @"p.*";
            string sqlWhere = "";
            // string sqlWhere = "    and ProjectStatus in (2,4,5)";
            //if (search.ArticleClassId != "")
            //{
            //    sqlWhere += " and c.MenuId=' " + search.ArticleClassId + "' ";
            //}
            if (search.AuditStatus != "")
            {
                sqlWhere += " and AuditStatus= " + search.AuditStatus + " ";
            }
            return base.PageGet<ProjectEx>(view, cols, " R_Project ", sqlWhere, "id", view.GetSqlOrder());
        }


        public List<ProjectEx> QueryHotProject()
        {
            //const string cols = @"p.*";
            // string sql = "select top 6 * from View_GetProducts  where AuditStatus = 210 order by BeginTime desc ";
            string sql = "select viewpro.*,com.CompanyName from View_GetProducts as viewpro left join (select MemberId, CompanyName from U_Company where Status=2) as com on viewpro.MemberId=com.MemberId ";
            // string sqlWhere = "    and ProjectStatus in (2,4,5)";
            //if (search.ArticleClassId != "")
            //{
            //sqlWhere += " and c.MenuId=' " + search.ArticleClassId + "' ";
            //}
            return base.Query<ProjectEx>(sql, null);
            //return base.PageGet<ProjectEx>(view, cols, " View_GetProducts p left join C_Article c on p.Id=c.Source", sqlWhere, "p.id", view.GetSqlOrder());
        }

        #region 项目流程
        public int InsertPorjectFlow(R_ProjectFlow flow)
        {
            return (int)base.Insert(flow);
        }
        #endregion


        public IList<R_ProjectFlow> SelectPorjectFlow(int porjectid)
        {
            const string strSql = "select * from dbo.R_ProjectFlow where ProjectId=@ProjectId order by Id desc ";
            return base.Query<R_ProjectFlow>(strSql, new { ProjectId = porjectid });
        }

        /// <summary>
        /// 首页数据统计 项目总数，注册人数，累计投资
        /// </summary>
        /// <returns></returns>
        public Change.Index GetIndexStatics()
        {
            //AuditStatus=210 表示项目发布成功
            string sql = @"                          select ((select COUNT(distinct ProjectId) from T_Investment)+(select COUNT(id) from R_Project where AuditStatus=210))AS ProjectNums
                                  ,(select COUNT(ID) from U_Member) AS RegisterNums,
                                  convert(decimal(18,2),(( select SUM(TenderAmt) from T_Investment 
                                  where [status]=1)+( select ISNULL(SUM(Amount),0) from [R_ProjectOrder]  where PayStatus=1))/10000) AS TenderAmt  ";
            return base.Get<Change.Index>(sql, null);
        }

        //获取当天众筹项目的最大编号
        public string GetProjectBiaoNo()
        {
            string sql = @" SELECT TOP 1 BiaoNo FROM [R_Project] 
                            WHERE SUBSTRING(BiaoNo,0,13)='QJSP'+CONVERT(varchar(100), GETDATE(), 112) 
                            ORDER BY id DESC";
            return base.Get<string>(sql, null);
        }

        //查询某个项目下所有支付的订单
        public List<R_ProjectOrder> QueryProjectOrderList(int projectId, int payStatus)
        {
            string sql = @"SELECT [Id],[ProjectId],[ProjectItemId],[Amount],[MemberId],[PayTime],[PayType],[AddTime],[SerialNumber],[Contacts]
                            ,[ContactNumber],[Remark],[PayStatus],[NeedInvoice],[UserAddress],[ItemFreight],[InvoiceTitle]
                            FROM [zhongchou].[dbo].[R_ProjectOrder] where ProjectId=@ProjectId and PayStatus=@PayStatus";
            return base.Query<R_ProjectOrder>(sql, new { ProjectId = projectId, PayStatus = payStatus });
        }
    }
}
